﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Reflection;
using System.IO;
using System.Diagnostics;
using System.Text.RegularExpressions;
using System.Data.OleDb;

namespace template_dll
{
    public partial class ChildForm : Form
    {
        public ChildForm()
        {
            InitializeComponent();
        }

        private SqlConnection con;
        private DataTable dt_max = new DataTable();
        private int drugcode = 0;
        private CurrencyManager cm;
        private DataTable dt_comp = new DataTable();
        
        public static bool DisplayForm(System.Windows.Forms.Form theMDIParent)
        {
            ChildForm f = new ChildForm();
            f.MdiParent = theMDIParent;
            f.WindowState = FormWindowState.Maximized;
            f.Show();
            return true;
        }

        private string GetServ(string filename)
        {
            string str = "";
            using (StreamReader streamReader = new StreamReader(filename))
            {
                while (!streamReader.EndOfStream)
                {
                    str += streamReader.ReadLine();
                }
            }
            return str;
        }

        private void ChildForm_Load(object sender, EventArgs e)
        {
            con = new SqlConnection(@"Server=" + GetServ("serv.txt") + ";Database=PharmOLAPDW;Integrated Security=True");
            Maksavit();
            ComboBoxRef();

        }

        private void ComboBoxRef()
        {
            try
            {
                comboBox1.Items.Clear();
                comboBox1.Text = "";
                dt_comp.Clear();
                dt_comp.Columns.Clear();

                SqlCommand com = con.CreateCommand();
                com.CommandType = CommandType.StoredProcedure;
                com.CommandText = "pas_DAC_CompetitorsSel";
                if (con.State == ConnectionState.Closed) con.Open();
                dt_comp.Load(com.ExecuteReader());

                if (dt_comp.Rows.Count > 0)
                    foreach (DataRow dr in dt_comp.Rows)
                        comboBox1.Items.Add(dr[1].ToString());
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }

        }

        private void Maksavit()
        {
            try
            {
                SqlCommand com = con.CreateCommand();
                com.CommandType = CommandType.StoredProcedure;
                com.CommandText = "pas_DAC_MaksavitSel";
                if(con.State == ConnectionState.Closed) con.Open();
                dt_max.Clear();
                dataGridView1.DataSource = null;
                dt_max.Load(com.ExecuteReader());
                dataGridView1.DataSource = dt_max.DefaultView;
                cm = (CurrencyManager)dataGridView1.BindingContext[dataGridView1.DataSource];
                dataGridView1.Sort(dataGridView1.Columns[1], ListSortDirection.Ascending);
                dataGridView1.Columns[1].Width = (dataGridView1.Width - dataGridView1.Columns[0].Width) / 2;
                dataGridView1.Columns[2].Width = (dataGridView1.Width - dataGridView1.Columns[0].Width) / 2;
                statusStrip1.Items[0].Text = "Количество строк: " + dataGridView1.RowCount.ToString();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }

        }
        private void ExpToExcel(object sender, EventArgs e)
        {
            SaveFileDialog sfd = new SaveFileDialog();
            sfd.FileName = "Привязки Максавита";
            sfd.Filter = "Файл CSV | *.csv";
            if (sfd.ShowDialog() == DialogResult.OK)
            {
                SaveDGVToCSVfile(sfd.FileName.ToString(), dataGridView1, true);
            }
        }

        public static bool SaveDGVToCSVfile(string filename, DataGridView table, bool openInExcel)
        {
            try
            {
                StreamWriter sw = new StreamWriter(filename, false, Encoding.Unicode);

                List<int> col_n = new List<int>();
                foreach (DataGridViewColumn col in table.Columns)
                    if (col.Visible)
                    {
                        sw.Write(col.HeaderText + "\t");
                        col_n.Add(col.Index);
                    }
                sw.WriteLine();
                int x = table.RowCount;
                if (table.AllowUserToAddRows) x--;

                for (int i = 0; i < x; i++)
                {
                    if (table.Rows[i].Visible)
                    {
                        for (int y = 0; y < col_n.Count; y++)
                            sw.Write(table[col_n[y], i].Value + "\t");
                        sw.Write(" \r\n");
                    }
                }
                sw.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Ошибка!", MessageBoxButtons.OK, MessageBoxIcon.Stop);
                return false;
            }

            if (openInExcel)
            {
                try
                {
                    Process newProc = Process.Start("excel.exe", "\"" + filename + "\"");
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, "Ошибка!", MessageBoxButtons.OK, MessageBoxIcon.Stop);
                }
            }
            return true;
        }

        private void button1_Click(object sender, EventArgs e)
        {
            try
            {
                if (drugcode > 0)
                {
                    if (textBox2.Text != "")
                    {
                        if (comboBox1.Text != "")
                        {
                            SqlCommand ins = con.CreateCommand();
                            ins.CommandType = CommandType.StoredProcedure;
                            ins.CommandText = "pas_DAC_MaksavitInsDel";

                            ins.Parameters.Add("@drugcode", SqlDbType.Int);
                            ins.Parameters["@drugcode"].Value = drugcode;

                            ins.Parameters.Add("@max", SqlDbType.VarChar, 100);
                            ins.Parameters["@max"].Value = textBox2.Text;

                            ins.Parameters.Add("@comp", SqlDbType.Int);
                            if (dt_comp.Rows.Count > 0)
                                foreach (DataRow dr in dt_comp.Rows)
                                    if(dr[1].ToString() == comboBox1.Text)
                                        ins.Parameters["@comp"].Value = Convert.ToInt32(dr[0]);

                            if (con.State == ConnectionState.Closed) con.Open();
                            ins.ExecuteNonQuery();

                            Maksavit();
                        }
                        else
                        {
                            MessageBox.Show("Укажите конкурента");
                            comboBox1.Focus();
                        }
                    }
                    else
                    {
                        MessageBox.Show("Введите наименование товара у конкурента");
                        textBox2.Focus();
                    }
                }
                else
                {
                    MessageBox.Show("Выберите товар");
                    textBox1.Focus();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }

        private void button3_Click(object sender, EventArgs e)
        {
            try
            {
                if (textBox1.Text != "")
                {
                    SqlCommand dr = con.CreateCommand();
                    dr.CommandType = CommandType.StoredProcedure;
                    dr.CommandText = "pas_AllDrugsSel";
                    dr.Parameters.Add("@drug", SqlDbType.NVarChar, 20);
                    dr.Parameters["@drug"].Value = textBox1.Text;
                    if (con.State == ConnectionState.Closed) con.Open();
                    DataTable dt = new DataTable();
                    dt.Load(dr.ExecuteReader());

                    Form2 f2 = new Form2();
                    f2.dataGridView1.DataSource = dt.DefaultView;
                    for (int i = 0; i < f2.dataGridView1.ColumnCount; i++)
                    {
                        if (i != 1 && i != 2) f2.dataGridView1.Columns[i].Visible = false;
                    }
                    f2.dataGridView1.Columns[2].Width = f2.dataGridView1.Width - f2.dataGridView1.Columns[1].Width;
                    f2.dataGridView1.Focus();
                    f2.ShowDialog();
                    textBox1.Text = f2.dataGridView1.CurrentRow.Cells[2].Value.ToString();
                    drugcode = Convert.ToInt32(f2.dataGridView1.CurrentRow.Cells[1].Value);
                    f2.Close();
                    f2 = null;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            try
            {
                if (dataGridView1.CurrentRow.Index > -1)
                {
                    string s = dataGridView1.CurrentRow.Cells[0].Value.ToString() + " - " + dataGridView1.CurrentRow.Cells[1].Value.ToString() + " - " + dataGridView1.CurrentRow.Cells[2].Value.ToString() + "\nКонкурент <" + dataGridView1.CurrentRow.Cells[3].Value.ToString() + ">";
                    DialogResult dialogResult = MessageBox.Show(s, "Удалить запись?", MessageBoxButtons.YesNo);
                    if (dialogResult == DialogResult.Yes)
                    {
                        SqlCommand ins = con.CreateCommand();
                        ins.CommandType = CommandType.StoredProcedure;
                        ins.CommandText = "pas_DAC_MaksavitInsDel";

                        ins.Parameters.Add("@drugcode", SqlDbType.Int);
                        ins.Parameters["@drugcode"].Value = Convert.ToInt32(dataGridView1.CurrentRow.Cells[0].Value);

                        ins.Parameters.Add("@max", SqlDbType.VarChar, 100);
                        ins.Parameters["@max"].Value = dataGridView1.CurrentRow.Cells[2].Value.ToString();

                        ins.Parameters.Add("@del", SqlDbType.Bit);
                        ins.Parameters["@del"].Value = 1;

                        ins.Parameters.Add("@comp", SqlDbType.Int);
                        ins.Parameters["@comp"].Value = dataGridView1.CurrentRow.Cells[4].Value.ToString();

                        if (con.State == ConnectionState.Closed) con.Open();
                        ins.ExecuteNonQuery();

                        Maksavit();
                    }
                }
                else
                {
                    MessageBox.Show("Выберите строку");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }

        }

        private void textBox3_Enter(object sender, EventArgs e)
        {
            HideShowRows();
        }

        private void textBox3_TextChanged(object sender, EventArgs e)
        {
            HideShowRows();
        }

        private void HideShowRows()
        {
            try
            {
                cm.SuspendBinding();
                int vis = 0;
                foreach (DataGridViewRow r in dataGridView1.Rows)
                {
                    r.Visible = true;
                    if (!Regex.Match(Convert.ToString(dataGridView1[dataGridView1.SortedColumn.Name, r.Index].Value), textBox3.Text, RegexOptions.IgnoreCase).Success)
                        r.Visible = false;
                    if (r.Visible) vis++;
                }
                statusStrip1.Items[0].Text = "Количество строк: " + vis.ToString();
                cm.ResumeBinding();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }

        private void textBox1_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter) button3_Click(sender, e);
        }

        private void dataGridView1_CellMouseDoubleClick(object sender, DataGridViewCellMouseEventArgs e)
        {
            button2_Click(sender, e);
        }

        private void button5_Click(object sender, EventArgs e)
        {
            if (comboBox1.Text != "")
            {
                MessageBox.Show("Файл Excel\nТолько одна страница\nНа странице два столбца:\n\t1)Код товара в МАП\n\t2)Наименование товара у конкурента\nУ столбцов обязательно должны быть имена", "Требования к файлу");
                OpenFileDialog opf = new OpenFileDialog();
                opf.Filter = "Файлы Excel (1997-2007)|*.XLSX;*.XLS";
                if (opf.ShowDialog() == DialogResult.OK)
                {
                    string filename = opf.FileName;
                    string ConStr = String.Format("Provider=Microsoft.ACE.OLEDB.12.0; Data Source={0}; Extended Properties=Excel 8.0;", filename);
                    DataSet ds = new DataSet("EXCEL");
                    OleDbConnection cn = new OleDbConnection(ConStr);

                    if (cn.State == ConnectionState.Closed) cn.Open();
                    DataTable schemaTable = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { }); //{ null, null, null, null }
                    string sheet1 = (string)schemaTable.Rows[0].ItemArray[2];
                    string select = String.Format("SELECT * FROM [{0}]", sheet1);
                    OleDbDataAdapter ad = new OleDbDataAdapter(select, cn);
                    ad.Fill(ds);
                    DataTable tb = ds.Tables[0];
                    cn.Close();

                    if (tb.Rows.Count > 0)
                    {
                        Form3 form = new Form3();
                        form.dataGridView1.DataSource = tb.DefaultView;
                        if (form.ShowDialog() == DialogResult.OK)
                        {
                            SqlCommand ins = con.CreateCommand();
                            ins.CommandType = CommandType.StoredProcedure;
                            ins.CommandText = "pas_DAC_MaksavitInsDel";
                            ins.Parameters.Add("@drugcode", SqlDbType.Int);
                            ins.Parameters.Add("@max", SqlDbType.VarChar, 100);
                            ins.Parameters.Add("@comp", SqlDbType.Int);
                            if (dt_comp.Rows.Count > 0)
                                foreach (DataRow dr in dt_comp.Rows)
                                    if (dr[1].ToString() == comboBox1.Text)
                                        ins.Parameters["@comp"].Value = Convert.ToInt32(dr[0]);

                            foreach (DataRow dr in tb.Rows)
                            {
                                if (dr[0] != DBNull.Value && dr[0].ToString() != "" && dr[1] != DBNull.Value && dr[1].ToString() != "")
                                {
                                    ins.Parameters["@drugcode"].Value = dr[0];
                                    ins.Parameters["@max"].Value = dr[1].ToString();
                                    if (con.State == ConnectionState.Closed) con.Open();
                                    ins.ExecuteNonQuery();
                                }
                            }
                            MessageBox.Show("Файл залит");
                            Maksavit();
                        }
                        form.Close();
                    }
                    else
                        MessageBox.Show("Нет ни одной строки в файле");
                }
            }
            else
            {
                MessageBox.Show("Укажите конкурента");
                comboBox1.Focus();
            }
        }

        private void button6_Click(object sender, EventArgs e)
        {
            Form4 form = new Form4();
            if (form.ShowDialog() == DialogResult.OK)
            {
                SqlCommand ins = con.CreateCommand();
                ins.CommandType = CommandType.StoredProcedure;
                ins.CommandText = "pas_DAC_CompetitorIns";
                ins.Parameters.AddWithValue("@name",form.textBox1.Text);
                if (con.State == ConnectionState.Closed) con.Open();
                ins.ExecuteNonQuery();
            }
            form.Close();
            ComboBoxRef();
        }
    }
}
